package com.financialDog.javaTool.utils;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.resource.ResourceUtil;
import com.alibaba.fastjson.JSONObject;
import com.financialDog.javaTool.common.PropertyConstant;
import com.financialDog.javaTool.reportModel.KeyValue;
import javafx.scene.control.TableView;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TreeItem;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;


import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.Date;
import java.util.HashMap;
import java.util.Random;

public class ExportUtil {

    static public String exportReportToExcel(String filePath,
                                             String fileName ,
                                             TableView balanceTableView,
                                             TableView profitTableView,
                                             TableView moneyTableView,
                                             TableView accountTableView) throws IOException {
        SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory,
        Sheet balanceSh = wb.createSheet(PropertyConstant.BALANCE_TABLE_CHINESE_NAME);
        Sheet profitSh = wb.createSheet(PropertyConstant.PROFIT_TABLE_CHINESE_NAME);
        Sheet moneySh = wb.createSheet(PropertyConstant.MONEY_TABLE_CHINESE_NAME);
        Sheet accountSh = wb.createSheet(PropertyConstant.ACCOUNT_TABLE_CHINESE_NAME);

        exportBalance(balanceSh, balanceTableView);
        exportProfit(profitSh, profitTableView);
        exportMoney(moneySh, moneyTableView);
        exportAccount(accountSh, accountTableView);

        //生成名字
        if (fileName == null){
            Date date = new Date();
            fileName = DateUtil.format(date, "YYYY-MM-DD HH:MM");
        }

        String actuallyName = filePath + "/" + fileName + ".xlsx";
        FileOutputStream outputStream = new FileOutputStream(actuallyName);

        wb.write(outputStream);
        wb.close();
        outputStream.close();

        return StringUtils.EMPTY;
    };

    static private Boolean exportBalance(Sheet sh, TableView balanceTableView){
        // 这个是业务数据
        //表头都是双层表头
        Row row1 = sh.createRow(0);
        Row row2 = sh.createRow(1);
        int[] titleIndex1 ={0};
        int[] titleIndex2 ={0};
        HashMap<String, Integer> indexMap = new HashMap<>();

        balanceTableView.getColumns().forEach(e->{
            TableColumn tableColumn = (TableColumn) e;
            String header = tableColumn.getText();
            Cell cell1 = row1.createCell(titleIndex1[0]);
            int startCol = titleIndex1[0];
            titleIndex1[0]++;
            cell1.setCellValue(header);
            int childSize = tableColumn.getColumns().size();
            if (childSize != 0){
                tableColumn.getColumns().forEach(c->{
                    TableColumn t = (TableColumn) c;
                    String childHeader = t.getText();
                    Cell cell12 = row2.createCell(titleIndex2[0]);
                    cell12.setCellValue(childHeader);
                    if (childHeader.equals("合并报表") || childHeader.equals("占比")){
                        childHeader = childHeader + header;//增加年份
                    }
                    indexMap.put(childHeader, titleIndex2[0]);
                    titleIndex2[0]++;
                    //i[0]++;
                });
                if (childSize != 1){
                    titleIndex1[0] += childSize - 1;
                    CellRangeAddress region = new CellRangeAddress(0, 0, startCol, titleIndex1[0] -1 );
                    sh.addMergedRegion(region);
                }
            }
        });
        // 资产负债表合并
        int[] dataRowIndex = {2};
        balanceTableView.getItems().forEach(e->{
            HashMap<String,String> itemMap = (HashMap<String,String>)e;
            int currentDataRowIndex = dataRowIndex[0];
            Row dataRow = sh.createRow(currentDataRowIndex);
            itemMap.forEach((key,value)->{
                Integer columnIndex = indexMap.get(key);
                Cell dataCell = dataRow.createCell(columnIndex);
                dataCell.setCellValue(value);
            });
            dataRowIndex[0]++;
        });

        return true;
    }


    static private Boolean exportProfit(Sheet sh, TableView profitTableView){
        // 这个是业务数据
        //表头都是双层表头
        Row row1 = sh.createRow(0);
        Row row2 = sh.createRow(1);
        int[] titleIndex1 ={0};
        int[] titleIndex2 ={0};
        HashMap<String, Integer> indexMap = new HashMap<>();

        profitTableView.getColumns().forEach(e->{
            TableColumn tableColumn = (TableColumn) e;
            String header = tableColumn.getText();
            Cell cell1 = row1.createCell(titleIndex1[0]);
            int startCol = titleIndex1[0];
            titleIndex1[0]++;
            cell1.setCellValue(header);
            int childSize = tableColumn.getColumns().size();
            if (childSize != 0){
                tableColumn.getColumns().forEach(c->{
                    TableColumn t = (TableColumn) c;
                    String childHeader = t.getText();
                    Cell cell12 = row2.createCell(titleIndex2[0]);
                    cell12.setCellValue(childHeader);
                    if (childHeader.equals("合并报表") || childHeader.equals("占比")){
                        childHeader = childHeader + header;//增加年份
                    }
                    indexMap.put(childHeader, titleIndex2[0]);
                    titleIndex2[0]++;
                    //i[0]++;
                });
                if (childSize != 1){
                    titleIndex1[0] += childSize - 1;
                    CellRangeAddress region = new CellRangeAddress(0, 0, startCol, titleIndex1[0] -1 );
                    sh.addMergedRegion(region);
                }
            }
        });
        // 资产负债表合并
        int[] dataRowIndex = {2};
        profitTableView.getItems().forEach(e->{
            HashMap<String,String> itemMap = (HashMap<String,String>)e;
            int currentDataRowIndex = dataRowIndex[0];
            Row dataRow = sh.createRow(currentDataRowIndex);
            itemMap.forEach((key,value)->{
                Integer columnIndex = indexMap.get(key);
                Cell dataCell = dataRow.createCell(columnIndex);
                dataCell.setCellValue(value);
            });
            dataRowIndex[0]++;
        });

        return true;
    }


    static private Boolean exportMoney(Sheet sh, TableView moneyTableView){
        // 这个是业务数据
        //表头都是双层表头
        Row row1 = sh.createRow(0);
        Row row2 = sh.createRow(1);
        int[] titleIndex1 ={0};
        int[] titleIndex2 ={0};
        HashMap<String, Integer> indexMap = new HashMap<>();

        moneyTableView.getColumns().forEach(e->{
            TableColumn tableColumn = (TableColumn) e;
            String header = tableColumn.getText();
            Cell cell1 = row1.createCell(titleIndex1[0]);
            int startCol = titleIndex1[0];
            titleIndex1[0]++;
            cell1.setCellValue(header);
            int childSize = tableColumn.getColumns().size();
            if (childSize != 0){
                tableColumn.getColumns().forEach(c->{
                    TableColumn t = (TableColumn) c;
                    String childHeader = t.getText();
                    Cell cell12 = row2.createCell(titleIndex2[0]);
                    cell12.setCellValue(childHeader);
                    if (childHeader.equals("合并报表") || childHeader.equals("占比")){
                        childHeader = childHeader + header;//增加年份
                    }
                    indexMap.put(childHeader, titleIndex2[0]);
                    titleIndex2[0]++;
                    //i[0]++;
                });
                if (childSize != 1){
                    titleIndex1[0] += childSize - 1;
                    CellRangeAddress region = new CellRangeAddress(0, 0, startCol, titleIndex1[0] -1 );
                    sh.addMergedRegion(region);
                }
            }
        });
        // 资产负债表合并
        int[] dataRowIndex = {2};
        moneyTableView.getItems().forEach(e->{
            HashMap<String,String> itemMap = (HashMap<String,String>)e;
            int currentDataRowIndex = dataRowIndex[0];
            Row dataRow = sh.createRow(currentDataRowIndex);
            itemMap.forEach((key,value)->{
                Integer columnIndex = indexMap.get(key);
                Cell dataCell = dataRow.createCell(columnIndex);
                dataCell.setCellValue(value);
            });
            dataRowIndex[0]++;
        });

        return true;
    }

    static private Boolean exportAccount(Sheet sh, TableView accountTableView){
        // 这个是业务数据
        //表头都是双层表头
        Row row1 = sh.createRow(0);
        int[] titleIndex ={0};
        HashMap<String, Integer> indexMap = new HashMap<>();
        HashMap<String, HashMap<String, HashMap<String, String>>> dataMap = new HashMap<>();

        //1.先解析数据
        Cell projectCell = row1.createCell(0);
        projectCell.setCellValue("项目");
        accountTableView.getColumns().forEach(e->{
            TableColumn tableColumn = (TableColumn) e;
            String header = tableColumn.getText();
            indexMap.put(header, titleIndex[0]);
            titleIndex[0]++;
            Cell headCell = row1.createCell(titleIndex[0]);
            headCell.setCellValue(header);
        });

        // 指标表合并
        String str = ResourceUtil.readUtf8Str("config/calculate_account.json");
        JSONObject accountObject = JSONObject.parseObject(str);
        HashMap<String, JSONObject> accountMap = new HashMap<>();
        for(String key : accountObject.keySet()){
            JSONObject keyObject = accountObject.getJSONObject(key);
            String name = keyObject.getString(PropertyConstant.NAME_KEY);
            accountMap.put(name, keyObject);
        }

        accountTableView.getItems().forEach(e->{
            HashMap<String,String> itemMap = (HashMap<String,String>)e;
            String accountName = itemMap.get("指标");
            JSONObject account = accountMap.get(accountName);
            String catalog = account.getString(PropertyConstant.CATALOG_KEY);
            HashMap<String, String> lineDataMap = new HashMap<>();
            itemMap.forEach((key,value)->{
                Integer columnIndex = indexMap.get(key);
                if (columnIndex != 0){
                    //指标那行不要
                    lineDataMap.put(key, value);
                }
            });

            HashMap<String , HashMap<String, String>> tempMap = new HashMap<>();
            if (dataMap.containsKey(catalog)){
                tempMap = dataMap.get(catalog);
                tempMap.put(accountName, lineDataMap);
            }else{
                tempMap.put(accountName, lineDataMap);
                dataMap.put(catalog, tempMap);
            }
        });

        int[] dataRowIndex = {1};
        dataMap.forEach( (catalog, tempMap)->{
            Row dataLine = sh.createRow(dataRowIndex[0]);
            int start = dataRowIndex[0];
            tempMap.forEach((account, dataLineMap)->{
                Row accountLine = dataLine;
                if (dataRowIndex[0] != start){
                    accountLine = sh.createRow(dataRowIndex[0]);
                }
                Cell accountCell = accountLine.createCell(1);
                accountCell.setCellValue(account);
                Row dataValueLine = accountLine;
                dataLineMap.forEach((year, dataValue)->{
                    Integer index = indexMap.get(year);
                    if (index != 0 ){
                        Row line = dataValueLine;
                        Cell dataLineCell = line.createCell(index + 1);
                        dataLineCell.setCellValue(dataValue);
                    }
                });
                dataRowIndex[0]++;
            });
            int end = dataRowIndex[0] - 1 ;
            Cell catalogCell = dataLine.createCell(0);
            catalogCell.setCellValue(catalog);
            if (end  != start){
                CellRangeAddress region = new CellRangeAddress(start, end, 0, 0);
                sh.addMergedRegion(region);
            }
        });

        return true;
    }
}




